package com.lr.excel.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lr.excel.dao.ExcelDataDao;
import com.lr.excel.entity.ExcelData;
import com.lr.excel.listener.ExcelDataListener;
import com.lr.excel.service.ExcelDataService;
import com.lr.excel.service.ExcelOperateService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

/**
 * @author liurui
 * @ad Java一对一辅导，手把手带你拿高薪！
 * @WeChat：liuliang70
 * @date 2024/6/14 16:08
 */
@Service
public class ExcelOperateServiceImpl implements ExcelOperateService {

    @Autowired
    private ExcelDataService excelDataService;

    @Autowired
    private ExcelDataDao excelDataDao;
    private static final int BATCH_SIZE = 10000;  // 每批次处理的数据量

    @Override
    public void importDataFromExcelWithEasyExcel() {
        String excelFilePath = "million_rows_data.xlsx";
        long startTime = System.currentTimeMillis();
        EasyExcel.read(excelFilePath, ExcelData.class, new ExcelDataListener(excelDataService)).sheet().doRead();
        System.out.println("单线程导入100万条数据到MySQL所需时间：" + (System.currentTimeMillis() - startTime) / 1000 + "s");
    }

    @Override
    public void importDataFromExcelWithEasyExcelASync() {
        String excelFilePath = "million_rows_sheet_data.xlsx";
        long startTime = System.currentTimeMillis();
        int sheetNumber = 20;
        ExecutorService executorService = Executors.newFixedThreadPool(sheetNumber);
        for (int i = 0; i < sheetNumber; i++) {
            int finalSheetNumber = i;
            executorService.submit(() -> {
                EasyExcel.read(excelFilePath, ExcelData.class, new ExcelDataListener(excelDataService))
                        .sheet(finalSheetNumber)
                        .doRead();
            });
        }
        //线程池不再接受新的任务，但会继续执行已提交的任务。当所有任务执行完毕后，线程池会自动关闭。
        executorService.shutdown();
        try {
            //等待所有任务执行完成
            executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.MICROSECONDS);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("多线程异步导入100万条数据到MySQL所需时间：" + (System.currentTimeMillis() - startTime) / 1000 + "s");
    }

    @Override
    public void exportDataToExcel() {
        long startTime = System.currentTimeMillis();
        String excelFilePath = "export_million_rows_data.xlsx";
        int offset = 0;
        ExcelWriter excelWriter = EasyExcel.write(excelFilePath, ExcelData.class).build();

        while (true) {
            List<ExcelData> dataBatch = excelDataDao.getExcelDataBatch(offset, BATCH_SIZE);
            if (dataBatch.isEmpty()) {
                break;  // 如果数据批次为空，退出循环
            }

            WriteSheet writeSheet = EasyExcel.writerSheet("Data").build();
            excelWriter.write(dataBatch, writeSheet);

            offset += BATCH_SIZE;
        }

        excelWriter.finish();
        System.out.println("单线程导入100万条数据从MySQL导出到Excel所需时间：" + (System.currentTimeMillis() - startTime) / 1000 + "s");
    }



}
